-- 物料变更报表
alter PROCEDURE materialRequirementsReport @begingmt_create DATETIME,
                                            @endgmt_create DATETIME,
                                            @PageIndex INT,
                                            @PageSize INT,
                                            @TotalCount INT OUTPUT,
    --产线
                                            @production_line VARCHAR(200),
    --物料编码
                                            @wlbm VARCHAR(200),
                                            @BatchNumberList VARCHAR(2000)
AS
BEGIN
    SET NOCOUNT ON;

    -- 计算分页的起始位置
    DECLARE @Offset INT = (@PageIndex - 1) * @PageSize;
--------------------------------------------------------------------------------------------

    select mes_order_create_time "MES订单创建时间",
           production_line       "产线",
           customer_code         "客户代码",
           customer_name         "客户名称",
           a.batch_number        "工单号",
           item_code             "产品名称",
           qty                   "数量",
           d.material_total      "领料合计",
           d.loss_num            "总需求数",
           b.process_name        "工序名称",
           before_change_info    "原物料类型",
           after_change_info     "新物料类型",
           material_code         "原物料编码",
           new_material_code     "新物料编码",
           original_opening_info "原开纸信息",
           new_opening_info      "新开纸信息",
           old_paper_open        "原纸张开数",
           new_paper_open        "新纸张开数",
           b.changed_by          "更改人",
           b.change_time         "更改时间"


    from mes_bo_import_data_change_log b
             left join mes_batch_order a on a.id = b.bo_id
             left join mes_bo_process c on a.id = c.bo_id and b.process_name = c.process_name
             outer apply (select top 1 *
                          from dbo.mes_bo_process_detail d
                          where d.bo_id = a.id
                            and d.bo_process_id = c.id) d
    where b.change_type = 'MATERIAL'

------------------------------------------------------------------------------------------------------


      AND (@begingmt_create IS NULL OR b.change_time >= @begingmt_create)
      AND (@endgmt_create IS NULL OR b.change_time <= @endgmt_create)
      AND (@production_line IS NULL OR a.production_line = @production_line)
      AND (@wlbm IS NULL OR b.material_code = @wlbm)
      AND (@BatchNumberList IS NULL OR
           EXISTS (SELECT 1 FROM STRING_SPLIT(@BatchNumberList, ' ') WHERE a.batch_number LIKE '%' + TRIM(value) + '%'))

    order by a.gmt_create desc
    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY;

    SELECT @TotalCount = COUNT(1)

    ---------------------------------------------------------------------

    from mes_batch_order a
             join mes_bo_import_data_change_log b on a.id = b.bo_id
             left join mes_bo_process c on a.id = c.bo_id and b.process_name = c.process_name
             outer apply (select top 1 *
                          from dbo.mes_bo_process_detail d
                          where d.bo_id = a.id
                            and d.bo_process_id = c.id) d
    where b.change_type = 'MATERIAL'

    -------------------------------------------


      AND (@begingmt_create IS NULL OR b.change_time >= @begingmt_create)
      AND (@endgmt_create IS NULL OR b.change_time <= @endgmt_create)
      AND (@production_line IS NULL OR a.production_line = @production_line)
      AND (@wlbm IS NULL OR b.material_code = @wlbm)
      AND (@BatchNumberList IS NULL OR
           EXISTS (SELECT 1 FROM STRING_SPLIT(@BatchNumberList, ' ') WHERE a.batch_number LIKE '%' + TRIM(value) + '%'))

END